package mysqls

import (
	"errors"
	"strings"

	"qingbing/xzutils/utils"

	"gorm.io/gorm"
)

type SqlBuilder struct {
	*gorm.DB
}

// 新建 SQL 构建器
func NewSqlBuilder(db ...*gorm.DB) *SqlBuilder {
	if len(db) == 0 {
		return &SqlBuilder{DB: DB}
	} else {
		return &SqlBuilder{DB: db[0]}
	}
}

// 返回 sql 查询条件
func (sb *SqlBuilder) Tx() *gorm.DB {
	return sb.DB
}

// 返回 sql 查询条件
func (sb *SqlBuilder) Valid(value any) bool {
	return !utils.IsNil(value)
}

// 指定查询主模型
func (sb *SqlBuilder) Model(mod any) *SqlBuilder {
	sb.DB = sb.DB.Model(mod)
	return sb
}

// Table 查询: 主表
func (sb *SqlBuilder) Table(name any, args ...interface{}) *SqlBuilder {
	switch name.(type) {
	case string:
		sb.DB = sb.DB.Table(name.(string), args...)
	default:
		sb.DB = sb.DB.Table(TableName(name)+" AS m", args...)
	}
	return sb
}

// join 表
// Join("Account")
// Join("JOIN emails ON emailsb.user_id = usersb.id AND emailsb.email = ?", "jinzhu@example.org")
// Join("Account", DB.Select("id").Where("user_id = usersb.id AND name = ?", "someName")
func (sb *SqlBuilder) Join(query string, args ...interface{}) *SqlBuilder {
	sb.DB = sb.DB.Joins(query, args...)
	return sb
}

// left join 表
func (sb *SqlBuilder) LeftJoin(name any, alias string, connect string, args ...interface{}) *SqlBuilder {
	sb.DB = sb.DB.Joins("LEFT JOIN `"+TableName(name)+"` `"+alias+"` ON("+connect+")", args...)
	return sb
}

// right join 表
func (sb *SqlBuilder) RightJoin(name any, alias string, connect string, args ...interface{}) *SqlBuilder {
	sb.DB = sb.DB.Joins("RIGHT JOIN `"+TableName(name)+"` `"+alias+"` ON("+connect+")", args...)
	return sb
}

// order 条件
// Order("id").Order("name DESC")
// Order("id, name DESC")
func (sb *SqlBuilder) Order(order any) *SqlBuilder {
	sb.DB = sb.DB.Order(order)
	return sb
}

// group 子语句
func (sb *SqlBuilder) Group(name string) *SqlBuilder {
	sb.DB = sb.DB.Group(name)
	return sb
}

// having 子语句
func (sb *SqlBuilder) Having(query interface{}, args ...interface{}) *SqlBuilder {
	sb.DB = sb.DB.Having(query, args...)
	return sb
}

// select 字段
// 1. Select([]string{"name", "age"})
// 2. Select("name", "age")
// 3. Select("COALESCE(age,?)", 42)
func (sb *SqlBuilder) Select(fields any, args ...interface{}) *SqlBuilder {
	sb.DB = sb.DB.Select(fields, args...)
	return sb
}

// limit 子条件
func (sb *SqlBuilder) Limit(limit int) *SqlBuilder {
	sb.DB = sb.DB.Limit(limit)
	return sb
}

// offset 子条件
func (sb *SqlBuilder) Offset(offset int) *SqlBuilder {
	sb.DB = sb.DB.Offset(offset)
	return sb
}

// And: 条件查询
// 1. And(1): PrimaryKey
// 2. And([]int{1, 2}): PrimaryKey
// 3. And([]int64{20, 21, 22}): PrimaryKey
// 4. And("id = ?", "string_primary_key")
// 5. And(User{Age: 20})
// 7. And(map[string]interface{}{"age": 20})
func (sb *SqlBuilder) And(query interface{}, args ...interface{}) *SqlBuilder {
	sb.DB = sb.Where(query, args...)
	return sb
}

// Or 条件查询
func (sb *SqlBuilder) Or(query interface{}, args ...interface{}) *SqlBuilder {
	sb.DB = sb.DB.Or(query, args...)
	return sb
}

// like 条件查询
func (sb *SqlBuilder) like(field string, value any, direction TDBLikeDirection, operateType TDBLinkType) *SqlBuilder {
	if utils.IsNil(value) {
		return sb
	}
	val, ok := utils.GetReflectValue(value).Interface().(string)
	if !ok || val == "" {
		return sb
	}
	var likeStr string
	switch direction {
	case DB_LIKE_LEFT:
		likeStr = val + "%"
	case DB_LIKE_RIGHT:
		likeStr = "%" + val
	default:
		likeStr = "%" + val + "%"
	}
	if operateType == DB_LINK_OR {
		return sb.OrWhere(field, "LIKE", likeStr)
	}
	return sb.AndWhere(field, "LIKE", likeStr)
}

// And 条件构建
func (sb *SqlBuilder) AndWhere(field string, operate string, value any) *SqlBuilder {
	if sb.Valid(value) {
		sb.DB = sb.Where(field+" "+operate+" ?", value)
	}
	return sb
}

// And: = 条件查询
func (sb *SqlBuilder) AndEqual(field string, value any, allowEmpty ...bool) *SqlBuilder {
	if !sb.Valid(value) {
		return sb
	}
	val := utils.GetReflectValue(value).Interface()
	if !utils.IsZero(val) {
		return sb.AndWhere(field, "=", value)
	}
	if len(allowEmpty) > 0 && allowEmpty[0] {
		return sb.AndWhere(field, "=", value)
	}
	return sb
}

// And: 左 like 条件查询
func (sb *SqlBuilder) AndLeftLike(field string, value any) *SqlBuilder {
	return sb.like(field, value, DB_LIKE_LEFT, DB_LINK_AND)
}

// And: 右 like 条件查询
func (sb *SqlBuilder) AndRightLike(field string, value any) *SqlBuilder {
	return sb.like(field, value, DB_LIKE_RIGHT, DB_LINK_AND)
}

// And: 右 like 条件查询
func (sb *SqlBuilder) AndLike(field string, value any) *SqlBuilder {
	return sb.like(field, value, DB_LIKE_BOTH, DB_LINK_AND)
}

// And: fields like
func (sb *SqlBuilder) AndFieldsLike(fields []string, val *string, directions ...TDBLikeDirection) *SqlBuilder {
	if !sb.Valid(val) || *val == "" {
		return sb
	}

	direction := DB_LIKE_BOTH
	if len(directions) > 0 {
		direction = directions[0]
	}

	var likeStr string
	switch direction {
	case DB_LIKE_LEFT:
		likeStr = *val + "%"
	case DB_LIKE_RIGHT:
		likeStr = "%" + *val
	default:
		likeStr = "%" + *val + "%"
	}

	args := []any{}
	conds := []string{}
	for _, field := range fields {
		args = append(args, likeStr)
		conds = append(conds, field+" LIKE ?")
	}
	return sb.And(strings.Join(conds, " OR "), args...)
}

// And: 右 like 条件查询
func (sb *SqlBuilder) AndIn(field string, value []any) *SqlBuilder {
	return sb.AndWhere(field, "IN", value)
}

// And: between 条件查询
func (sb *SqlBuilder) AndBetween(field string, low, high any) *SqlBuilder {
	if sb.Valid(low) && sb.Valid(high) {
		sb.DB = sb.Where(field+" BETWEEN ? AND ?", low, high)
	}
	return sb
}

// Or 条件构建
func (sb *SqlBuilder) OrWhere(field string, operate string, value any) *SqlBuilder {
	if sb.Valid(value) {
		return sb.Or(field+" "+operate+" ?", value)
	}
	return sb
}

// Or: = 条件查询
func (sb *SqlBuilder) OrEqual(field string, value any) *SqlBuilder {
	return sb.OrWhere(field, "=", value)
}

// Or: 左 like 条件查询
func (sb *SqlBuilder) OrLeftLike(field string, value any) *SqlBuilder {
	return sb.like(field, value, DB_LIKE_LEFT, DB_LINK_OR)
}

// Or: 右 like 条件查询
func (sb *SqlBuilder) OrRightLike(field string, value any) *SqlBuilder {
	return sb.like(field, value, DB_LIKE_RIGHT, DB_LINK_OR)
}

// Or: 右 like 条件查询
func (sb *SqlBuilder) OrLike(field string, value any) *SqlBuilder {
	return sb.like(field, value, DB_LIKE_BOTH, DB_LINK_OR)
}

// Or: 右 like 条件查询
func (sb *SqlBuilder) OrIn(field string, value []any) *SqlBuilder {
	return sb.OrWhere(field, "IN", value)
}

// Or: between 条件查询
func (sb *SqlBuilder) OrBetween(field string, low, high any) *SqlBuilder {
	if sb.Valid(low) && sb.Valid(high) {
		return sb.Or(field+" BETWEEN ? AND ?", low, high)
	}
	return sb
}

// Valid 事件有效条件查询
func (sb *SqlBuilder) ValidTime(isValid *bool, startField, endField string, now any, operateType ...TDBLinkType) *SqlBuilder {
	if isValid != nil {
		ot := DB_LINK_AND
		if len(operateType) > 0 {
			ot = operateType[0]
		}
		var qs string
		if *isValid == true {
			qs = startField + " IS NULL OR " + startField + " < ? AND " + endField + " IS NULL OR " + endField + " > ?"
		} else {
			qs = "(" + startField + " IS NOT NULL AND " + startField + " > ?) OR (" + endField + " IS NOT NULL AND " + endField + " < ?)"
		}
		if ot == DB_LINK_OR {
			sb.Or(qs, now, now)
		} else {
			sb.And(qs, now, now)
		}
	}
	return sb
}

// First 查询
// 1. First(&user)
// 2. First(&map[string]interface{})
// 3. First(&user, 1): PrimaryKey
// 4. First(&user, "id = ?", "string_primary_key")
func (sb *SqlBuilder) First(dest interface{}, conds ...interface{}) {
	tx := sb.DB.First(dest, conds...)
	if tx.Error != nil && errors.Is(tx.Error, gorm.ErrRecordNotFound) {
		panic("Query Failed.")
	}
}

// Take 查询
// 1. Take(&user)
// 2. Take(&map[string]interface{})
// 3. Take(&user, 1): PrimaryKey
// 4. Take(&user, "id = ?", "string_primary_key")
func (sb *SqlBuilder) Take(dest interface{}, conds ...interface{}) {
	tx := sb.DB.Take(dest, conds...)
	if tx.Error != nil && errors.Is(tx.Error, gorm.ErrRecordNotFound) {
		panic("Query F.")
	}
}

// Last 查询
// 1. Last(&user)
// 2. Last(&map[string]interface{})
// 3. Last(&user, 1): PrimaryKey
// 4. Last(&user, "id = ?", "string_primary_key")
func (sb *SqlBuilder) Last(dest interface{}, conds ...interface{}) {
	tx := sb.DB.Last(dest, conds...)
	if tx.Error != nil && errors.Is(tx.Error, gorm.ErrRecordNotFound) {
		panic("Query F.")
	}
}

// Find 查询
// 1. Find(&users)
// 2. Find(&map[string]interface{})
// 3. Find(&users, 1): PrimaryKey
// 4. Find(&users, []int{1, 2}): PrimaryKey
// 5. Find(&users, "id = ?", "string_primary_key")
// 6. Find(&users, User{Age: 20})
// 7. Find(&users, map[string]interface{}{"age": 20})
func (sb *SqlBuilder) Find(dest interface{}, conds ...interface{}) {
	tx := sb.DB.Find(dest, conds...)
	if tx.Error != nil && errors.Is(tx.Error, gorm.ErrRecordNotFound) {
		panic("Query F.")
	}
}

// 数据扫描到目标中
// 1. Scan(&map[string]interface{})
// 2. Scan(&struct)
func (sb *SqlBuilder) Scan(dest interface{}) {
	tx := sb.DB.Scan(dest)
	if tx.Error != nil && errors.Is(tx.Error, gorm.ErrRecordNotFound) {
		panic("Query F.")
	}
}

// 查询到 map 中
func (sb *SqlBuilder) FindToMap() []map[string]any {
	res := []map[string]any{}
	tx := sb.DB.Find(&res)
	if tx.Error != nil {
		if errors.Is(tx.Error, gorm.ErrRecordNotFound) {
			return []map[string]any{}
		}
		panic("Query F.")
	}
	return res
}
